using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using Deepcode.Flux.Core.Data;
using Deepcode.Flux.Core.Services.Security;
using Deepcode.Flux.Core.Systems.Audit;

namespace Deepcode.Flux.Core.Systems.CMS.DataTaxonomy
{
	/// <summary>
	/// Manager class for the data taxonomy tree
	/// </summary>
	public class DataTaxonomyTree : Deepcode.Flux.Core.Data.BaseManager
	{
		#region Constructors
		public DataTaxonomyTree() : base() { }
		public DataTaxonomyTree(SqlConnection CurrentConnection) : base(CurrentConnection) { }
		public DataTaxonomyTree(SqlConnection CurrentConnection, SqlTransaction CurrentTransaction) : base(CurrentConnection, CurrentTransaction) { }
		#endregion
		#region Data Retrieval Methods
		/// <summary>
		/// Navigates the taxonomy tree to find a node by it's full text path.
		/// Specify the path as /Node name/Node name/Node name/
		/// </summary>
		/// <param name="path"></param>
		/// <returns></returns>
		public DataTaxonomyNode GetPath(string path)
		{
			// Process the path - change backslash to forward slash
			string Path2 = path.Replace('\\', '/');
			// Split the path on it's elements
			string [] Elements = Path2.Split('/');
			
			// Start searching at the root
			int CurrentParentID = -1;
			DataTaxonomyNode CurrentNode = null;

			// Loop over each textual element
			foreach (string e in Elements)
			{
				// Ignore empty strings
				if( e.Trim() == "" ) continue;
				// Get the node with the name specified at the current sibling level
				CurrentNode = GetByNameAndParent( CurrentParentID, e );
				// Return null if not found
				if (CurrentNode == null) return null;
				// Set the sibling level for the next level search
				CurrentParentID = CurrentNode.PK_ID;
			}
			// Return the last successful result			
			return CurrentNode;
		}
		/// <summary>
		/// Gets all of the tree nodes for a particular parent
		/// </summary>
		/// <returns></returns>
		public DataTaxonomyNode [] GetForParentID( int ParentID )
		{
			List<DataTaxonomyNode> results = new List<DataTaxonomyNode>();
			DataTable result = new DataTable();

			EstablishConnection();
			SqlDataAdapter da = this.CreateDataAdapter("coreDTGetByParentID");
			da.SelectCommand.Parameters.Add( SQLUtility.ParameterIn("@id", SqlDbType.Int, 4, ParentID) );
			da.Fill( result );
			ReleaseConnection();

			foreach (DataRow r in result.Rows)
				results.Add(new DataTaxonomyNode(r));

			return results.ToArray();
		}
		/// <summary>
		/// Gets the node with the specified name within sibling level specified by parent ID
		/// </summary>
		/// <param name="ParentID"></param>
		/// <param name="Name"></param>
		/// <returns></returns>
		public DataTaxonomyNode GetByNameAndParent(int ParentID, string Name)
		{
			DataTable result = new DataTable();
			EstablishConnection();
			SqlDataAdapter da = this.CreateDataAdapter("coreDTGetByNameAndParent");
			da.SelectCommand.Parameters.Add(SQLUtility.ParameterIn("@FK_ParentID", SqlDbType.Int, 4, ParentID));
			da.SelectCommand.Parameters.Add(SQLUtility.ParameterIn("@Name", SqlDbType.NVarChar, 100, Name));
			da.Fill(result);
			ReleaseConnection();

			if (result == null) return null;
			if (result.Rows.Count < 1) return null;

			return new DataTaxonomyNode(result.Rows[0]);
		}
		/// <summary>
		/// Gets the specific tree node by it's ID
		/// </summary>
		/// <param name="ID"></param>
		/// <returns></returns>
		public DataTaxonomyNode GetByID(int ID)
		{
			DataTable result = new DataTable();
			EstablishConnection();
			SqlDataAdapter da = this.CreateDataAdapter("coreDTGetByID");
			da.SelectCommand.Parameters.Add(SQLUtility.ParameterIn("@id", SqlDbType.Int, 4, ID));
			da.Fill(result);
			ReleaseConnection();

			if (result == null) return null;
			if (result.Rows.Count < 1) return null;

			return new DataTaxonomyNode(result.Rows[0]);
		}
		#endregion
		#region Record Save/Delete
		/// <summary>
		/// Saves the data taxonomy node to the database
		/// </summary>
		/// <param name="group"></param>
		/// <returns></returns>
		public int Save(DataTaxonomyNode node)
		{
			EstablishConnection();
			SqlCommand cmd = this.CreateSqlCommand("coreDTSave");
			SqlParameter result = SQLUtility.ParameterInOut("@PK_ID", SqlDbType.Int, 4, node.PK_ID);
			cmd.Parameters.Add(result);
			cmd.Parameters.Add( SQLUtility.ParameterIn("@FK_ParentID", SqlDbType.Int, 4, node.FK_ParentID));
			cmd.Parameters.Add(SQLUtility.ParameterIn("@Name", SqlDbType.NVarChar, 100, node.Name));
			cmd.ExecuteNonQuery();
			ReleaseConnection();

			if (node.PK_ID == -1)
				node.PK_ID = (int)result.Value;

			return node.PK_ID;
		}
		/// <summary>
		/// Deletes the specified node from the database and recurses
		/// </summary>
		/// <param name="GroupID"></param>
		public bool DeleteByID(int NodeID)
		{
			try
			{
				EstablishConnection();
				BeginTransaction();

				// Get and delete all children first
				foreach (DataTaxonomyNode child in GetForParentID(NodeID))
					if( ! DeleteByID(child.PK_ID) ) throw new Exception("Could not delete a child");

				// Finally delete this one itself
				SqlCommand cmd = this.CreateSqlCommand("coreDTDelete");
				cmd.Parameters.Add(SQLUtility.ParameterIn("@id", SqlDbType.Int, 4, NodeID));
				cmd.ExecuteNonQuery();
				EndTransaction(EndTransactionMode.COMMIT);
				ReleaseConnection();

				return true;
			}
			catch
			{
				try { EndTransaction(EndTransactionMode.ROLLBACK); } catch { }
				try { ReleaseConnection(); } catch { }

				return false;
			}
		}
		#endregion
		#region Taxonomy to Content Linking
		/// <summary>
		/// For an item of content, sets the linked taxonomy ID's
		/// </summary>
		/// <param name="ContentObjectID"></param>
		/// <param name="TaxonomyIDs"></param>
		public void SetForContent( int ContentObjectID, TaxonomySelections TaxonomyIDs )
		{
			try
			{
				// Start the transaction and establish a connection
				EstablishConnection();
				BeginTransaction();
			
				// Delete any items not in the supplied list
				DeleteUnlisted( ContentObjectID, TaxonomyIDs );
				
				// Create any in the list which doesn't already exist
				// Start by loading the current selections from the database
				TaxonomySelections selections = this.GetForContent( ContentObjectID );
				// Loop through each one specified
				foreach( int Selection in TaxonomyIDs.Keys )
					if( ! (selections.Contains( Selection )))
					{
						// Add it
						SqlCommand cmd = this.CreateSqlCommand("coreDTLinkAdd");
						cmd.Parameters.Add(SQLUtility.ParameterIn("@ContentID", SqlDbType.Int, 4, ContentObjectID));
						cmd.Parameters.Add(SQLUtility.ParameterIn("@TaxonomyID", SqlDbType.Int, 4, Selection ));
						cmd.ExecuteNonQuery();
					}

				EndTransaction(EndTransactionMode.COMMIT);
				ReleaseConnection();
			}
			catch( Exception e )
			{
				try { EndTransaction(EndTransactionMode.ROLLBACK); } catch { }
				try { ReleaseConnection(); } catch { }

				throw e;
			}
		}
		/// <summary>
		/// Utility to delete any records in the content taxonomy tables that
		/// are not specified in the current taxonomy selections.
		/// </summary>
		/// <param name="ContentObjectID"></param>
		/// <param name="TaxonomyIDs"></param>
		protected void DeleteUnlisted(int ContentObjectID, TaxonomySelections TaxonomyIDs)
		{
			string SQL = "";
			if (TaxonomyIDs.Keys.Length > 0)
			{
				// Make a list of keys
				string KeyList = "";
				foreach (int i in TaxonomyIDs.Keys)
				{
					if (KeyList != "") KeyList += ",";
					KeyList += i.ToString();
				}

				// Setup the SQL
				SQL = String.Format(
					"DELETE FROM ContentTaxonomy WHERE FK_ContentID=@ContentID " +
					"AND FK_DataTAxonomyID NOT IN ({0})", KeyList);
			}
			else
			{
				SQL = "DELETE FROM ContentTaxonomy WHERE FK_ContentID=@ContentID";
			}
			// Execute it
			SqlCommand cmd = this.CreateSqlCommand(SQL);
			cmd.CommandType = CommandType.Text;
			cmd.Parameters.Add(SQLUtility.ParameterIn("@ContentID", SqlDbType.Int, 4, ContentObjectID));
			cmd.ExecuteNonQuery();
		}
		/// <summary>
		/// For an item of content, gets the linked taxonomy ID's
		/// </summary>
		/// <param name="ContentObjectID"></param>
		/// <returns></returns>
		public TaxonomySelections GetForContent(int ContentObjectID)
		{
			TaxonomySelections selections = new TaxonomySelections();

			DataTable result = new DataTable();
			EstablishConnection();
			SqlDataAdapter da = this.CreateDataAdapter("coreDTLinkGetForContent");
			da.SelectCommand.Parameters.Add( SQLUtility.ParameterIn("@ContentID", SqlDbType.Int, 4, ContentObjectID) );
			da.Fill( result );
			ReleaseConnection();

			foreach (DataRow r in result.Rows)
				selections.Add( SQLUtility.DBNull(r["FK_DataTaxonomyID"], -1));

			return selections;
		}
		#endregion
	}
}
